package br.com.usjt.locadoraweb.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import br.com.usjt.locadoraweb.model.Vehicle;
import br.com.usjt.locadoraweb.model.AcessoBD;
import br.com.usjt.locadoraweb.util.Util;

public class VehicleDAO {
	boolean status = false;
	List<Vehicle> resultado;
	Util util = new Util();

	Vehicle venicle;
	String table;

	public VehicleDAO(Vehicle vehicle) {
		try {
			updateVehicle(venicle);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public VehicleDAO() {
		// TODO Auto-generated constructor stub
	}

	public boolean save(Vehicle vehicle) throws Exception {
		table = "vehicle";
		String insertVehicle = "INSERT INTO vehicle (id, plaque, chassis, model, groupvehicle, accessory, ratekmtraveled, ratekmfree, rentedvehicle) "
				+ "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
		System.out.println("Antes " + insertVehicle);

		Connection conn = null;
		PreparedStatement stm = null;
		try {
			// obtem conexao com o banco
			//
			AcessoBD bd = new AcessoBD();
			conn = bd.obtemConexao();

			// cria prepared statement
			//
			stm = conn.prepareStatement(insertVehicle);

			stm.setLong(1, bd.obterProximoIdentificador(table));
			stm.setString(2, vehicle.getPlaque());
			stm.setString(3, vehicle.getChassis());
			stm.setString(4, vehicle.getModel());
			stm.setString(5, vehicle.getGroupVehicle());
			stm.setString(6, util.getFormatedDate(vehicle.getAcquisitionDate()));
			stm.setString(7, vehicle.getAccessory());
			stm.setDouble(8, Double.parseDouble(vehicle.getRateKmTraveled()));
			stm.setDouble(9, Double.parseDouble(vehicle.getRateKmFree()));
			stm.setString(10, vehicle.getRentedVehicle());
			//stm.setString(9, "0");

			System.out.println(stm.toString());

			// executa inserção do registro no banco
			stm.execute();
			status = true;
		} finally {
			// IMPORTANTE UTILIZACAO DO FINALLY PARA GARANTIR O FECHAMENTO
			// DA CONEXAO COM O BANCO DE DADOS.

			// fecha primeiro o statement
			//
			if (stm != null) {
				try {
					stm.close();
				} catch (SQLException e) {
					//
				}
			}
			// fecha a conexao
			//
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					//
				}
			}
		}
		System.out.println("DAO status " + status);
		return status;

	}

	public List<Vehicle> search() throws Exception {
		System.out.println("passou search de VehicleDAO");
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		resultado = new ArrayList<Vehicle>();

		try {
			String sql = "select * from vehicle order by id";

			AcessoBD bd = new AcessoBD();
			con = bd.obtemConexao();
			stmt = con.prepareStatement(sql);

			rs = stmt.executeQuery();

			while (rs.next()) {
				System.out.println("data:" + util.getStringDateToDate(rs.getString("acquisitiondate")));
				Vehicle vehicle = new Vehicle();
				vehicle.setId(rs.getInt("id"));
				vehicle.setPlaque(rs.getString("plaque"));
				vehicle.setChassis(rs.getString("chassis"));
				vehicle.setModel(rs.getString("model"));
				vehicle.setGroupVehicle(rs.getString("groupvehicle"));
				
				vehicle.setAcquisitionDate(util.getStringDateToDate(rs.getString("acquisitiondate")));
				vehicle.setAccessory(rs.getString("accessory"));
				vehicle.setRateKmFree(rs.getString("ratekmfree"));
				vehicle.setRateKmTraveled(rs.getString("ratekmtraveled"));
				vehicle.setRentedVehicle(rs.getString("rentedvehicle"));

				resultado.add(vehicle);
			}
			System.out.println(resultado.toString());

			return resultado;

		} catch (SQLException sq) {
			sq.printStackTrace();
			return new ArrayList<Vehicle>();

		} finally {
			// IMPORTANTE UTILIZACAO DO FINALLY PARA GARANTIR O FECHAMENTO
			// DA CONEXAO COM O BANCO DE DADOS.

			// fecha o resultset
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					//
				}
			}
			// fecha o statement
			//
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					//
				}
			}
			// fecha a conexao
			//
			if (con != null) {
				try {
					con.close();
				} catch (SQLException e) {
					status = false;
				}
			}
		}

	}

	public boolean updateVehicle(Vehicle vehicle) throws Exception {
		Connection con = null;
		PreparedStatement stmt = null;

		try {
			String sqlUpdateVehicle =
					"UPDATE vehicle SET plaque = ?, " + 
					"chassis = ?, model = ?, groupvehicle = ?, " +
					"accessory = ?, ratekmtraveled = ?, ratekmfree = ?, " +
					"rentedvehicle = ? WHERE id = ?";
			
			System.out.println(sqlUpdateVehicle.toString());
			
			AcessoBD bd = new AcessoBD();
			con = bd.obtemConexao();
			stmt = con.prepareStatement(sqlUpdateVehicle);
			

			stmt.setString(1, vehicle.getPlaque());
			stmt.setString(2, vehicle.getChassis());
			stmt.setString(3, vehicle.getModel());
			stmt.setString(4, vehicle.getGroupVehicle());
			stmt.setString(5, vehicle.getAccessory());
			stmt.setDouble(6, Double.parseDouble(vehicle.getRateKmTraveled()));
			stmt.setDouble(7, Double.parseDouble(vehicle.getRateKmFree()));
			stmt.setString(8, vehicle.getRentedVehicle());
			stmt.setInt(9, vehicle.getId());
			
			System.out.println(stmt.toString());

			stmt.execute();
			status = true;

		} catch (SQLException sq) {
			sq.printStackTrace();

		} finally {
			// IMPORTANTE UTILIZACAO DO FINALLY PARA GARANTIR O FECHAMENTO
			// DA CONEXAO COM O BANCO DE DADOS.

			// fecha o statement
			//
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					status = false;
				}
			}
			// fecha a conexao
			//
			if (con != null) {
				try {
					con.close();
				} catch (SQLException e) {
					//
				}
			}
		}
		return status;
	}
	
	public boolean delete(Vehicle vehicle) throws Exception{
		Connection con = null;
		PreparedStatement stmt = null;

		try {
			String sqlDeleteVehicle =
					"DELETE FROM vehicle WHERE id = ?";
			
			System.out.println(sqlDeleteVehicle.toString());
			
			AcessoBD bd = new AcessoBD();
			con = bd.obtemConexao();
			stmt = con.prepareStatement(sqlDeleteVehicle);
			stmt.setInt(1, vehicle.getId());
			
			System.out.println(stmt.toString());

			stmt.execute();
			status = true;

		} catch (SQLException sq) {
			sq.printStackTrace();

		} finally {
			// IMPORTANTE UTILIZACAO DO FINALLY PARA GARANTIR O FECHAMENTO
			// DA CONEXAO COM O BANCO DE DADOS.

			// fecha o statement
			//
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					status = false;
				}
			}
			// fecha a conexao
			//
			if (con != null) {
				try {
					con.close();
				} catch (SQLException e) {
					//
				}
			}
		}
		return status;
	}
}
